#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2018-09-19 14:33:10
# @Author  : Your Name (you@example.org)
# @Link    : link
# @Version : 1.0.0

from dbutils import dbconn
# online
from tables.BaseData import BaseData

# test
# import dbconn

def get_conn():
    connection = dbconn.main()
    return connection


def get_base_data():
    conn = get_conn()
    if conn:
        try:
            cursor = conn.cursor()
            sql = "select * from basedata"
            cursor.execute(sql)
            datalist = cursor.fetchall()
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
        return {'msg': 'success', 'code': '200', "detail":"查询成功" ,"data": datalist}
    else:
       return {"msg": 'e', 'code': '400', "detail":'DB ERROR'}


def update_base_data(_dict):
    conn = get_conn()
    if conn:
        try:
            cursor = conn.cursor()
            # python27版本
            # sql = "update basedata set name=\'%s\', type=\'%s\', cdate=\'%s\',cen_lon=\'%s\',\
            #     cen_lat=\'%s\',l_on_lon=\'%s\',l_on_lat=\'%s\', r_d_lon=\'%s\',r_d_lat=\'%s\'\
            #     ,author=\'%s\',cpath=\'%s\',recordp=\'%s\',rtime=\'%s\',format=\'%s\',\
            #     details=\'%s\' where id=%s" % (
            #     _dict['name'].encode("utf-8"), _dict['type'].encode("utf-8"), _dict['cdate'].encode("utf-8"), _dict['cen_lon'].encode("utf-8"),
            #     _dict['cen_lat'].encode("utf-8"), _dict['l_on_lon'].encode("utf-8"), _dict['l_on_lat'].encode("utf-8"), _dict['r_d_lon'].encode("utf-8"),
            #     _dict['r_d_lat'].encode("utf-8"), _dict['author'].encode("utf-8"), _dict['cpath'].encode("utf-8"), _dict['recordp'].encode("utf-8"),
            #     _dict['rtime'].encode("utf-8"), _dict['format'].encode("utf-8"), _dict['details'].encode("utf-8"),  _dict['id']
            # )
            # pyton36版本
            sql = "update basedata set name=\'%s\', type=\'%s\', cdate=\'%s\',cen_lon=\'%s\',\
                cen_lat=\'%s\',l_on_lon=\'%s\',l_on_lat=\'%s\', r_d_lon=\'%s\',r_d_lat=\'%s\'\
                ,author=\'%s\',cpath=\'%s\',recordp=\'%s\',rtime=\'%s\',format=\'%s\',\
                details=\'%s\' where id=%s" % (
                _dict['name'], _dict['type'], _dict['cdate'], _dict['cen_lon'],
                _dict['cen_lat'], _dict['l_on_lon'], _dict['l_on_lat'], _dict['r_d_lon'],
                _dict['r_d_lat'], _dict['author'], _dict['cpath'], _dict['recordp'],
                _dict['rtime'], _dict['format'], _dict['details'],  _dict['id']
            )
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
        return {'msg': 'success', 'code': '200', "detail": "数据更新成功"}
    pass


def delete_base_data(_uids):
    conn = get_conn()
    if conn:
        try:
            if len(_uids) == 0:
                return {"msg": 'no data delete', 'code': '1', "data": []}
            for _uid in _uids:
                cursor = conn.cursor()
                sql = "delete from basedata where id=%s" % (_uid)
                cursor.execute(sql)
            conn.commit()
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
        return {'msg': 'success', 'code': '200', "detail": "删除成功"}
    pass


def insert_base_data_single(_dict):
    conn = get_conn()
    if conn:
        try:
            cursor = conn.cursor()
            sql = "insert into basedata (name,type,cdate,cen_lon,cen_lat,l_on_lon,l_on_lat,r_d_lon,r_d_lat, \
            author, cpath, recordp, rtime, format, details) values (\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',  \
            \'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')" % (
                _dict['name'], _dict['type'], _dict['cdate'], _dict['cen_lon'],
                _dict['cen_lat'], _dict['l_on_lon'], _dict['l_on_lat'], _dict['r_d_lon'],
                _dict['r_d_lat'], _dict['author'], _dict['cpath'], _dict['recordp'],
                _dict['rtime'], _dict['format'], _dict['details']
            )
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
        return {'msg': 'success', 'code': '200', "detail":'入库成功'}
    pass


def insert_base_data_batch_win(_dict_list):
    conn = get_conn()
    if conn:
        try:
            cursor = conn.cursor()
            for i in range(len(_dict_list)):
                _dict = _dict_list[i]
                vals = []
                for k in _dict.keys():
                    if type(_dict[k]) is float or type(_dict[k]) is int:
                        vals.append(str(_dict[k]))
                    else:
                        vals.append(_dict[k])
                if len(_dict.keys()) == 15:
                    sql = "insert into basedata (author,format,l_on_lat,r_d_lat,cpath,name,cen_lon,type,details,r_d_lon, \
                            recordp,rtime,cen_lat,l_on_lon,cdate) values (\'%s\',\'%s\',\'%s\',\'%s\', \
                            \'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')" % tuple(vals)
                if len(_dict.keys()) == 11:
                    sql = "insert into basedata (author,format,cpath,details,type,cen_lon,name,recordp,rtime,cen_lat,cdate) \
                            values (\'%s\',\'%s\',\'%s\',\'%s\',\'%s\', \'%s\',\'%s\',\'%s\',\'%s\',\'%s\', \
                            \'%s\')" % tuple(vals)
                if len(_dict.keys()) == 9:
                    sql = "insert into basedata (author,format,cpath,details,type,name,recordp,rtime,cdate) values  \
                            (\'%s\',\'%s\',\'%s\', \'%s\',\'%s\',\'%s\',\'%s\',\'%s\', \'%s\')" % tuple(vals)
                cursor.execute(sql)
                conn.commit()
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
        return {'msg': 'success', 'code': '200', "detail": "入库成功"}
    pass

def insert_base_data_batch(_dict_list):
    conn = get_conn()
    if conn:
        try:
            cursor = conn.cursor()
            for i in range(len(_dict_list)):
                _dict = _dict_list[i]
                vals = []
                for k in _dict.keys():
                    if type(_dict[k]) is float or type(_dict[k]) is int:
                        vals.append(str(_dict[k]))
                    else:
                        vals.append(_dict[k])
                if len(_dict.keys()) == 15:
                    sql = "insert into basedata (name,type,cdate,cen_lon,cen_lat,l_on_lon,l_on_lat,r_d_lon,r_d_lat,author, \
                            cpath,recordp,rtime,format,details) values (\'%s\',\'%s\',\'%s\',\'%s\', \
                            \'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')" % tuple(vals)
                if len(_dict.keys()) == 11:
                    sql = "insert into basedata (name,type,cdate,cen_lon,cen_lat,author, \
                            cpath,recordp,rtime,format,details) values (\'%s\',\'%s\',\'%s\',\'%s\',\'%s\', \'%s\',\'%s\',\'%s\',\'%s\',\'%s\', \
                            \'%s\')" % tuple(vals)
                if len(_dict.keys()) == 9:
                    sql = "insert into basedata (name,type,cdate,author,cpath,recordp,rtime,format,details) values  \
                            (\'%s\',\'%s\',\'%s\', \'%s\',\'%s\',\'%s\',\'%s\',\'%s\', \'%s\')" % tuple(vals)
                cursor.execute(sql)
                conn.commit()
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
        return {'msg': 'success', 'code': '200', "detail": "入库成功"}
    pass


def get_author_date():
    pass


def insert_author_data():
    pass


# 验证用户登陆接口，返回用户等级
def verify_user(list):
    conn = get_conn()
    if conn:
        cursor = conn.cursor()
        try:
            username = list[0]
            psd = list[1]
            usersql = 'select password,level,des from userdata where name = \'%s\'' % (username)
            cursor.execute(usersql)
            data = cursor.fetchall()[0]
            if data:
                password = data[0]
                level = data[1]
                if password == psd:
                    return {"msg": "success", 'code': '200', "level": level}
                else:
                    return {"msg": "fail", 'code':'401', 'detail':"密码错误"}
            else:
                {"msg": "null", 'code': '402', 'detail':"用户不存在"}
        except Exception as e:
            return {"msg": e, 'code': '400', "detail":'DB ERROR'}
        finally:
            conn.close()
    pass


if __name__ == '__main__':
    get_base_data()
    pass
